const Book = require("../model/booksTest.model")
const borrow = require("../model/BorrowRecord.model")
const pool = require('../db/pool');


const { where, Op } = require("sequelize");

class BorrowRecord {
    // 借书信息查询操作
    // 借书信息查询操作
    async borrowingInformation(searchTerm) {
        try {
            const [rows] = await pool.query(`
                        SELECT 
                            b.id AS book_id,
                            b.title AS book_title,
                             br.id AS record_id,
                            b.category AS book_category,
                            b.author AS book_author,
                            br.borrower_name,
                            br.borrower_phone,
                            br.borrow_date,
                            br.due_date,
                            br.borrower_idcard
                           
                        FROM 
                            bookser b
                        LEFT JOIN 
                            borrow_records br ON b.id = br.book_id
                        WHERE 
                            (b.title LIKE ? OR 
                             br.borrower_name LIKE ? OR 
                             br.borrower_phone LIKE ?)
                            AND br.is_borrowed = 1`,
                [`%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`]);
            return rows;
        } catch (error) {
            console.error("查询错误:", error);
            return null; // 返回 null 或其他值以便调用方处理
        }
    }
    // 借书操作
    async borrowBook(data) {
        // todo:写入数据库
        const res = await borrow.create({
            ...data
        })
        return res
    }
    // 还书表更新
    // 修改书籍
    async updatebr(record_id, newData) {
        try {
            const [updated] = await borrow.update(newData, {
                where: { id: record_id }
            });

            if (updated) {
                console.log(`Book with ID ${record_id} was updated successfully.`);
                return true;
            } else {
                console.log(`Book with ID ${record_id} not found.`);
                return false;
            }
        } catch (error) {
            console.error('Error updating book:', error);
            throw error; // 处理错误
        }
    }

    async cs() {
        try {
            const [rows] = await pool.query(`
                SELECT
                    category,
                    COUNT(*) AS book_count
                FROM
                    bookser
                WHERE
                    is_deleted = 0
                GROUP BY
                    category
                ORDER BY
                    category;
            `);
            return rows
        } catch (error) {

        }
    }
    // 每天借书情况统计
    async ebb() {
        try {
            const [rows] = await pool.query(`
           SELECT borrow_date, COUNT(*) AS borrow_count
FROM borrow_records
GROUP BY borrow_date;`);
            return rows
        } catch (error) {
            return error
        }
    }

    // 统计每天还书记录
    async rbb() {
        try {
            const [rows] = await pool.query(`
          SELECT return_date, COUNT(*) AS borrow_count
FROM borrow_records
GROUP BY return_date;`);
            return rows
        } catch (error) {
            return error
        }
    }
    // 读书榜单

    async rl() {
        try {
            const [rows] = await pool.query(`
         SELECT borrower_name, COUNT(*) AS borrow_count
FROM borrow_records
GROUP BY borrower_name
ORDER BY borrow_count DESC
LIMIT 10;`);
            return rows
        } catch (error) {
            return error
        }
    }
    // 书籍欢迎榜单

    async wl() {
        try {
            const [rows] = await pool.query(`
        SELECT b.title, COUNT(br.book_id) AS borrow_count
FROM borrow_records br
JOIN bookser b ON br.book_id = b.id
GROUP BY b.id, b.title
ORDER BY borrow_count DESC
LIMIT 10;`);
            return rows
        } catch (error) {
            return error
        }
    }
    // 帮我统计馆内共有多少本书？借出去多少本？剩余多少本？
    async ttb() {
        try {
            const [rows] = await pool.query(`
                SELECT
                    COUNT(DISTINCT bs.id) AS total_books,
                    SUM(CASE WHEN br.is_borrowed = 1 THEN 1 ELSE 0 END) AS borrowed_books,
                    COUNT(DISTINCT bs.id) - SUM(CASE WHEN br.is_borrowed = 1 THEN 1 ELSE 0 END) AS remaining_books,
                    SUM(CASE WHEN br.borrow_date = CURRENT_DATE THEN 1 ELSE 0 END) AS today_borrowed,
                    SUM(CASE WHEN DATE(br.return_date) = CURRENT_DATE THEN 1 ELSE 0 END) AS today_returned
                FROM
                    bookser bs
                LEFT JOIN
                    borrow_records br ON bs.id = br.book_id
                WHERE
                    bs.is_deleted = 0;  -- 只统计未被删除的书籍
            `);
            return rows
        } catch (error) {
            return error
        }
    }
    // 逾期未归还统计
    async yq() {
        try {
            const [rows] = await pool.query(`
                SELECT
                    br.borrower_name,
                    br.borrower_idcard,
                    br.borrower_phone,
                    bs.title,
                    COUNT(*) AS overdue_count,
                    DATEDIFF(CURRENT_DATE, br.due_date) AS overdue_days
                FROM
                    borrow_records br
                JOIN
                    bookser bs ON br.book_id = bs.id
                WHERE
                    br.is_borrowed = 1 AND
                    br.due_date < CURRENT_DATE AND
                    (br.return_date IS NULL OR br.return_date = '') AND
                    bs.is_deleted = 0  -- 只统计未被删除的书籍
                GROUP BY
                    br.borrower_name, br.borrower_idcard, br.borrower_phone, bs.title
                ORDER BY
                    br.borrower_name;
            `);
            return rows
        } catch (error) {
            return error
        }
    }
    // 借书信息统计分页查询
    async searchborrow(limit,offset ) {
        console.log(111,limit,offset )
        limit = Number(limit)
        offset = Number(offset)
        try {
            // const [rows] = await pool.query(`
            //     SELECT 
            //         br.*, 
            //         b.title, 
            //         b.author, 
            //         b.publisher, 
            //         b.publish_year, 
            //         b.category
            //     FROM 
            //         borrow_records br
            //     JOIN 
            //         bookser b ON br.book_id = b.id
            //     ORDER BY 
            //         br.borrow_date DESC, id ASC
            //     LIMIT ? OFFSET ?`, 
            //     [limit, offset]);
            const [rows] = await pool.query(`
                SELECT 
                    br.*, 
                    b.title, 
                    b.author, 
                    b.publisher, 
                    b.publish_year, 
                    b.category
                FROM 
                    borrow_records br
                JOIN 
                    bookser b ON br.book_id = b.id
                WHERE 
                   br.is_borrowed = 1
                ORDER BY 
                    br.borrow_date DESC, br.id ASC
                LIMIT ? OFFSET ?`, 
                [limit, offset]);
                const [[counts]] = await pool.query('SELECT COUNT(*) AS total_records FROM borrow_records');
                const data={rows,counts}
            return data;
        } catch (error) {
            console.error("查询错误:", error);
            return null; // 返回 null 或其他值以便调用方处理
        }
    }

    // 筛选借书表中未还书的
    
}





module.exports = new BorrowRecord();